package com.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.model.Message;
import com.util.DBUtil;


public class MessageDAO{
	private final DBUtil db=new DBUtil();

	public boolean insert(Message gb){
		boolean flag=false;
		Connection con=db.getConnection();
		String sql = "insert into Message (name,title,content,time) values(?,?,?,?)";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, gb.getName());
			st.setString(2, gb.getTitle());
			st.setString(3, gb.getContent());
			Timestamp ts=new Timestamp(System.currentTimeMillis());
			String y=new SimpleDateFormat("yyyy/MM/dd").format(ts);
			st.setString(4, y);
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	public boolean update(Message u){
		boolean flag=false;
		Connection con=db.getConnection();
		String sql="update Message set title=?,content=? where id=?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, u.getTitle());
			st.setString(2, u.getContent());
			st.setInt(3, u.getId());
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	public boolean delete(Integer id){
		boolean flag=false;
		Connection con=db.getConnection();
		String sql="delete from Message where id=?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	public Message get(Integer id){
		Message gb=new Message();
		Connection con=db.getConnection();
		String sql="select * from Message where id=?";
		PreparedStatement st = null;
		ResultSet rs=null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			rs=st.executeQuery();
			if(rs.next()){
				gb.setId(rs.getInt("id"));
				gb.setName(rs.getString("name"));
				gb.setTitle(rs.getString("title"));
				gb.setContent(rs.getString("content"));
				gb.setTime(rs.getString("time"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return gb;
	}
	public List<Message> getAll(){
		return findSql("select * from Message order by id desc");
	}


	public List<Message> findSql(String sql){
		Message gb=null;
		List<Message> list=new ArrayList<Message>();
		Connection con=db.getConnection();
		Statement st = null;
		ResultSet rs=null;
		try {
			st = con.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next()){
				gb=new Message();
				gb.setId(rs.getInt("id"));
				gb.setName(rs.getString("name"));
				gb.setTitle(rs.getString("title"));
				gb.setContent(rs.getString("content"));
				gb.setTime(rs.getString("time"));
				list.add(gb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	
	
}

